PostgreSQL 安全管理 数据脱敏 视图

1 创建用户

这里创建两个用户：一个是 blog 用户，用于博客的应用程序，另外一个 anon_analyse 用户，用于展示和统计博客网站用户注册和获奖的信息。

CREATE USER    blog;
CREATE USER   anon_analyse;
GRANT ALL ON SCHEMA public to blog;

//屏幕输出：
CREATE ROLE
CREATE ROLE
GRANT

2 创建表和视图

2.1 切换为 blog 用户

SET ROLE blog;

//屏幕输出：
SET

2.2 创建用户信息表

CREATE TABLE   user_list(id int, user_name name, phone_number name);
INSERT INTO   user_list values(1, '张三', '15623897820');
INSERT INTO   user_list values(1, '李四', '15623788290');

//屏幕输出：
CREATE TABLE
INSERT 0 1
INSERT 0 1

\dt+

                                      List of relations
 Schema |   Name    | Type  | Owner | Persistence | Access method |    Size    | Description 
--------+-----------+-------+-------+-------------+---------------+------------+-------------
 public | user_list | table | blog  | permanent   | heap          | 8192 bytes | 
(1 row)

2.3 创建视图

这里使用 SELECT 语句中函数和拼接字符串功能，将敏感信息屏蔽达到脱敏的效果。使用 substring 函数提取电话号码的前三位和后四位。中间四位将会被 * 替换。

CREATE OR   REPLACE VIEW user_list_view AS
    SELECT 
            S.id,
            S.user_name,
            substring(S.phone_number, 1,3) ||   '******' ||substring(S.phone_number, 8,11) as phone_number
    FROM user_list AS S;
    

//屏幕输出：
CREATE VIEW

3 授予合适的权限

GRANT ALL ON   user_list_view TO anon_analyse ;

4 测试和验证

RESET ROLE;
SET ROLE   anon_analyse;

//屏幕输出：
RESET
SET

SELECT * FROM   user_list;

//屏幕输出：
ERROR:  permission denied for table user_list

SELECT * FROM   user_list_view;

//屏幕输出：
 id | user_name | phone_number  
----+-----------+---------------
  1 | 张三      | 156******7820
  1 | 李四      | 156******8290
(2 rows)